import datetime


from sqlalchemy import MetaData, create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import sessionmaker

from userModel import select

url = 'mysql+pymysql://root:9999@localhost:3306/zxzl?charset=utf8'
engine = create_engine(url, pool_size=10, max_overflow=3, echo_pool=True)
metadata = MetaData()
metadata.reflect(bind=engine, only=['dev', 'user_link_dev', 'dev_param',
                                    'dev_statistics_info', 'contract_link_dev',
                                    'swinery_link_dev'])
Base = automap_base(metadata=metadata)
session = sessionmaker(bind=engine)
Base.prepare()




############################################
#                 设备表                    #
############################################
#
# '生产日期'函数内自动添加
def insert_dev(deviceSN, cpuid, status):
        exist = search_dev(deviceSN=deviceSN)
        if exist:
                print('dev添加错误：设备号已存在！')
                return False
        Dev = Base.classes.dev
        sess = session()

        # 只存入日期部分
        now = datetime.datetime.now()
        date = datetime.datetime.date(now)
        dev = Dev(deviceSN=deviceSN, cpuid=cpuid, prod_time=date, status=status)
        try:
                sess.add(dev)
                sess.commit()
                sess.close()
                # print('dev插入成功！')
                return True
        except Exception as e:
                print('dev添加错误：数据库错误', e)
                return False


# 传入参数device_id, deviceSN, prod_time, status四选一
def delete_dev(device_id=None, deviceSN=None, prod_time=None, status=None):    # <<<<<<<<<<<<<<存在性判断
        sess = session()
        Dev = Base.classes.dev

        if device_id:
                dev = sess.query(Dev).filter_by(id=device_id).all()
        elif deviceSN:
                dev = sess.query(Dev).filter_by(deviceSN=deviceSN).all()
        elif prod_time:
                dev = sess.query(Dev).filter_by(prod_time=prod_time).all()
        elif status:
                dev = sess.query(Dev).filter_by(status=status).all()
        else:
                print('删除dev失败！')
                return False

        if not dev:     # 查找不存在，跳出
                print('dev不存在的查询！')
                return False

        try:
                for i in dev:
                        sess.delete(i)
                sess.commit()
                sess.close()
                print('删除dev成功！')
                return True
        except Exception as e:
                print(e)
                return False


# ①更改设备状态：device_id, status
# ②更改设备号：device_id, deviceSN
def update_dev(device_id, status=None, deviceSN=None):
        exist = search_dev(device_id=device_id)
        if not exist:
                print('dev修改错误：设备id不存在')
                return False
        sess = session()
        Dev = Base.classes.dev

        dev = sess.query(Dev).filter_by(id=device_id).first()
        if status:
                dev.status = status  # 修改设备状态
        elif deviceSN:
                dev.deviceSN = deviceSN
        else:
                dev.status = status
                dev.deviceSN = deviceSN

        try:
                sess.commit()
                sess.close()
                # print('修改dev成功！')
                return True
        except Exception as e:
                print('dev修改失败：数据库错误', e)
                return False


# ①：device_id（int/str或list）
# ②：deviceSN
# ③：device_id, status
def search_dev(deviceSN=None, prod_time=None, status=None, device_id=None):
        sess = session()
        Dev = Base.classes.dev

        if status and device_id:
                dev = sess.query(Dev).filter_by(status=status, id=device_id).all()
        elif deviceSN:
                dev = sess.query(Dev).filter_by(deviceSN=deviceSN).all()
        elif device_id:
                if isinstance(device_id, (str, int)):
                        dev = sess.query(Dev).filter_by(id=device_id).all()
                else:
                        dev = []
                        for i in device_id:
                                k = sess.query(Dev).filter_by(id=i).first()
                                if k:   # 只有存在时才添加（防止添加进None）
                                        dev.append(k)
        else:
                print('查找s_d错误！')
                return False
        if not dev:  # 查找不存在，跳出
                print('dev不存在的查询！')
                return False

        dev_dump = [{'id': dev[i].id, 'deviceSN': dev[i].deviceSN, 'cpuid': dev[i].cpuid,
                     'prod_time': dev[i].prod_time, 'status': dev[i].status}
                    for i in range(len(dev))]
        try:
                sess.commit()
                sess.close()
                # print(dev_dump)
                return dev_dump
        except Exception as e:
                print(e)
                return False


########################################
#                设备参数表              #
########################################
#
def insert_dev_param(device_id, **kwargs):   # <<<<<<<<<应该有批量设置参数
        exist = search_dev_param(device_id=device_id)
        if exist:
                print('插入的的d_p已存在！')
                return False

        sess = session()
        D_p = Base.classes.dev_param
        d_p = D_p(device_id=device_id)

        # 如果需要创建设备时就传入详细参数，直接放开这段注释并去掉上一句即可
        # empty_param = { 'wat_break': None, 'wat_flow_lo': None, 'wat_flow_hi': None,
        #                 'moto_temp_hi': None, 'feeder_empt': None, 'ultr_sens_dis': None,
        #                 'flow_sens_dis': None, 'str_line_dis': None, 'wat_val_dis': None,
        #                 'online_stat': None, 'act_stat': None, 'duetime_prom': None,
        #                 'vacant_time': None, 'trig_num': None, 'trig_interval': None,
        #                 'cali_quan': None, 'wat_fer_rate': None, 'feed_line_ctrl': None,
        #                 'weight_per_circ': None, 'vib_stat': None, 'vib_amp': None,
        #                 'pow_par': None}
        # wrong_param = []
        # for i in kwargs:        # 将kwargs与本地数据对齐
        #         if i in empty_param:
        #                 empty_param[i] = kwargs[i]
        #         else:
        #                 wrong_param.append(i)
        # if wrong_param:
        #         print('插入d_p错误：未识别的参数：', wrong_param)
        #         return False    # 包含错误的参数，返回插入失败
        #
        # d_p = D_p(device_id=device_id,
        #           wat_break=empty_param['wat_break'], wat_flow_lo=empty_param['wat_flow_lo'], wat_flow_hi=empty_param['wat_flow_hi'],
        #           moto_temp_hi=empty_param['moto_temp_hi'], feeder_empt=empty_param['feeder_empt'], ultr_sens_dis=empty_param['ultr_sens_dis'],
        #           flow_sens_dis=empty_param['flow_sens_dis'], str_line_dis=empty_param['str_line_dis'], wat_val_dis=empty_param['wat_val_dis'],
        #           online_stat=empty_param['online_stat'], act_stat=empty_param['act_stat'], duetime_prom=empty_param['wat_val_dis'],
        #           vacant_time=empty_param['vacant_time'], trig_num=empty_param['trig_num'], trig_interval=empty_param['trig_interval'],
        #           cali_quan=empty_param['cali_quan'], wat_fer_rate=empty_param['wat_fer_rate'], feed_line_ctrl=empty_param['feed_line_ctrl'],
        #           weight_per_circ=empty_param['weight_per_circ'], vib_stat=empty_param['vib_stat'], vib_amp=empty_param['vib_amp'],
        #           pow_par=empty_param['pow_par'])       # 将重合的两字典存库
        try:
                sess.add(d_p)
                sess.commit()
                sess.close()
                print('d_p插入成功！')
        except Exception as e:
                print(e)


# 设备参数表--删除
def delete_dev_param(device_id):
        exist = search_dev_param(device_id=device_id)
        if not exist:
                print('d_p删除错误：设备号不存在！')
                return False
        sess = session()
        D_p = Base.classes.dev_param

        d_p = sess.query(D_p).filter_by(device_id=device_id).all()

        try:
                for i in d_p:
                        sess.delete(i)
                sess.commit()
                sess.close()
                print('删除d_p成功')
                return True
        except Exception as e:
                print(e)
                return False


def update_dev_param(device_id, clean_all_old=False, **kwargs):
        """
        修改设备数据表
        :param device_id: 设备id
        :param kwargs: 其他参数
                {'wat_break': int(11),
                'wat_flow_lo': tinyint(1),
                'wat_flow_hi': tinyint(1),
                'moto_temp_hi': tinyint(1),
                'feeder_empt': tinyint(1),
                'ultr_sens_dis': tinyint(1),
                'flow_sens_dis': tinyint(1),
                'str_line_dis': tinyint(1),
                'wat_val_dis': tinyint(1),
                'online_stat': tinyint(1),
                'act_stat': tinyint(1),
                'duetime_prom': int(11),
                'vacant_time': float,
                'trig_num': float,
                'trig_interval': float,
                'cali_quan': float,
                'wat_fer_rate': float,
                'feed_line_ctrl': tinyint(1),
                'weight_per_circ': float,
                'vib_stat': tinyint(1),
                'vib_amp': float,
                'pow_par': tinyint(1)}

        :return:
        """
        exist = search_dev_param(device_id=device_id)   # 设备号存在性判断
        if not exist:
                print('修改d_p错误：设备id不存在！')
                return False

        sess = session()
        D_p = Base.classes.dev_param

        # local_param = {'wat_break': None, 'wat_flow_lo': None, 'wat_flow_hi': None,
        #                'moto_temp_hi': None, 'feeder_empt': None, 'ultr_sens_dis': None,
        #                'flow_sens_dis': None, 'str_line_dis': None, 'wat_val_dis': None,
        #                'online_stat': None, 'act_stat': None, 'duetime_prom': None,
        #                'vacant_time': None, 'trig_num': None, 'trig_interval': None,
        #                'cali_quan': None, 'wat_fer_rate': None, 'feed_line_ctrl': None,
        #                'weight_per_circ': None, 'vib_stat': None, 'vib_amp': None,
        #                'pow_par': None}
        local_param = exist[0]  # 查库找到上一版数据

        if clean_all_old:
                for i in local_param:
                        if i == 'id' or i == 'device_id':
                                continue
                        local_param[i] = None

        print(local_param)
        # 将kwargs与本地参数对齐
        wrong_param = []        # 存放传入的错误参数
        for i in kwargs:
                if i in local_param:
                        local_param[i] = kwargs[i]
                else:
                        wrong_param.append(i)
        if wrong_param: # 如果传入错误的参数则返回
                print('修改d_p错误：未识别的参数：', wrong_param)
                return False    # 放回修改修改失败

        # 修改库
        d_p = sess.query(D_p).filter_by(device_id=device_id).first()
        d_p.wat_break = local_param['wat_break']
        d_p.wat_flow_lo = local_param['wat_flow_lo']
        d_p.wat_flow_hi = local_param['wat_flow_hi']
        d_p.moto_temp_hi = local_param['moto_temp_hi']
        d_p.feeder_empt = local_param['feeder_empt']
        d_p.ultr_sens_dis = local_param['ultr_sens_dis']
        d_p.flow_sens_dis = local_param['flow_sens_dis']
        d_p.str_line_dis = local_param['str_line_dis']
        d_p.wat_val_dis = local_param['wat_val_dis']
        d_p.online_stat = local_param['online_stat']
        d_p.act_stat = local_param['act_stat']
        d_p.duetime_prom = local_param['duetime_prom']
        d_p.vacant_time = local_param['vacant_time']
        d_p.trig_num = local_param['trig_num']
        d_p.trig_interval = local_param['trig_interval']
        d_p.cali_quan = local_param['cali_quan']
        d_p.wat_fer_rate = local_param['wat_fer_rate']
        d_p.feed_line_ctrl = local_param['feed_line_ctrl']
        d_p.weight_per_circ = local_param['weight_per_circ']
        d_p.vib_stat = local_param['vib_stat']
        d_p.vib_amp = local_param['vib_amp']
        d_p.pow_par = local_param['pow_par']

        try:
                sess.commit()
                sess.close()
                print('修改d_p成功！')
                return True
        except Exception as e:
                print('修改d_p错误：数据库错误', e)
                return False


def search_dev_param(device_id):
        sess = session()
        D_p = Base.classes.dev_param

        d_p = sess.query(D_p).filter_by(device_id=device_id).all()

        if not d_p:
                print('查询s_d_p错误；设备不存在！')
                return False

        # 将读出的数据转换为[{}, {}]格式
        param_dump = [{'id': d_p[0].id, 'device_id': d_p[0].device_id,
                       'wat_break': d_p[0].wat_break, 'wat_flow_lo': d_p[0].wat_flow_lo, 'wat_flow_hi': d_p[0].wat_flow_hi,
                       'moto_temp_hi': d_p[0].moto_temp_hi, 'feeder_empt': d_p[0].feeder_empt, 'ultr_sens_dis': d_p[0].ultr_sens_dis,
                       'flow_sens_dis': d_p[0].flow_sens_dis, 'str_line_dis': d_p[0].str_line_dis, 'wat_val_dis': d_p[0].wat_val_dis,
                       'online_stat': d_p[0].online_stat, 'act_stat': d_p[0].act_stat, 'duetime_prom': d_p[0].duetime_prom,
                       'vacant_time': d_p[0].vacant_time, 'trig_num': d_p[0].trig_num, 'trig_interval': d_p[0].trig_interval,
                       'cali_quan': d_p[0].cali_quan, 'wat_fer_rate': d_p[0].wat_fer_rate, 'feed_line_ctrl': d_p[0].feed_line_ctrl,
                       'weight_per_circ': d_p[0].weight_per_circ, 'vib_stat': d_p[0].vib_stat, 'vib_amp': d_p[0].vib_amp,
                       'pow_par': d_p[0] .pow_par}]
        try:
                sess.commit()
                sess.close()
                # print(param_dump)
                return param_dump
        except Exception as e:
                print(e)
                return False


# ###########################################
#                设备统计数据表                #
# ###########################################
#
# 创建时'record_time'参数自动添加，其他参数默认为None
def insert_dev_statistics_info(device_id):

        exist = search_dev_statistics_info(device_id=device_id)
        if exist:
                print('插入d_s_i错误：设备已存在！')
                return False
        sess = session()
        D_s_i = Base.classes.dev_statistics_info

        d_s_i = D_s_i(device_id=device_id)
        try:
                sess.add(d_s_i)
                sess.commit()
                sess.close()
                print('添加d_s_i成功！')
                return True
        except Exception as e:
                print(e)
                return False


def delete_dev_statistics_info(device_id):
        exist = search_dev_statistics_info(device_id=device_id)
        if not exist:
                print('删除d_s_i错误：设备号不存在!')
                return False

        sess = session()
        D_s_i = Base.classes.dev_statistics_info

        d_s_i = sess.query(D_s_i).filter_by(device_id=device_id).all()
        try:
                for i in d_s_i:
                        sess.delete(i)
                sess.commit()
                sess.close()
                return True
        except Exception as e:
                sess.rollback()
                print('删除d_s_i错误:数据库错误')
                return False


def update_dev_statistics_info(device_id, clean_all_old=False, **kwargs):
        """
        修改设备统计数据表（dev_statistics_info）
        :param device_id:根据设备号查询
        :param kwargs:详细参数：（记录时间由函数内自动添加）
        {'str_trig_num': int(11),
        'ultr_ran_num': float,
        'equi_acc_mat': float,
        'equi_acc_wat': float}
        :return:
        """

        # 设备号存在性判断
        exist = search_dev_statistics_info(device_id=device_id)
        if not exist:
                print('修改d_s_i错误：设备号不存在')
                return False

        sess = session()
        D_s_i = Base.classes.dev_statistics_info
        d_s_i = sess.query(D_s_i).filter_by(device_id=device_id).first()

        local_info = exist[0]   # 上一版数据
        if clean_all_old:
                for i in local_info:
                        if i == 'id' or i == 'device_id' or i == 'record_time':
                                print(i)
                                continue
                        local_info[i] = None

        # 将kwargs与本地数据对齐
        wrong_param = []
        for i in kwargs:        # 防止传入错误的参数
                if i in local_info:
                        local_info[i] = kwargs[i]
                else:
                        wrong_param.append(i)
        if wrong_param:
                print('修改d_s_i错误：未识别的参数:', wrong_param)
                return False

        # 修改库
        d_s_i.str_trig_num = local_info['str_trig_num']
        d_s_i.ultr_ran_num = local_info['ultr_ran_num']
        d_s_i.equi_acc_mat = local_info['equi_acc_mat']
        d_s_i.equi_acc_wat = local_info['equi_acc_wat']
        # 存入该条修改的时间
        d_s_i.record_time = datetime.datetime.now().replace(microsecond=0)

        try:
                sess.commit()
                sess.close()
                print('修改d_s_i成功！')
                return True
        except Exception as e:
                print('插入d_s_i错误：数据库错误', e)
                return False


def search_dev_statistics_info(device_id):
        sess = session()
        D_s_i = Base.classes.dev_statistics_info

        d_s_i = sess.query(D_s_i).filter_by(device_id=device_id).all()
        if not d_s_i:
                print('查询d_s_i错误：设备不存在！')
                return False
        d_s_i_dump = [{'id': d_s_i[i].id, 'device_id': d_s_i[i].device_id, 'record_time': d_s_i[i].record_time,
                       'str_trig_num': d_s_i[i].str_trig_num, 'ultr_ran_num': d_s_i[i].ultr_ran_num,
                       'equi_acc_mat': d_s_i[i].equi_acc_mat, 'equi_acc_wat': d_s_i[i].equi_acc_wat}
                      for i in range(len(d_s_i))]

        try:
                sess.commit()
                sess.close()
                # print(d_s_i_dump)
                return d_s_i_dump
        except Exception as e:
                print(e)
                return False


###########################################
#               用户设备表                  #
###########################################
#
def insert_user_link_dev(user_id, device_id):
        exist = search_user_link_dev(user_id=user_id, device_id=device_id)
        if exist:
                print('u_l_d插入错误：重复创建')
                return False

        sess = session()
        U_l_d = Base.classes.user_link_dev
        user_exist = select(id=user_id)
        device_exist = search_dev(device_id=device_id)

        if user_exist and device_exist:
                u_l_d = U_l_d(device_id=device_id, user_id=user_id)
        else:
                print('u_l_d插入错误：不存在的插入信息！')
                return False
        try:
                sess.add(u_l_d)
                sess.commit()
                sess.close()
                print('u_l_d插入成功')
                return True
        except Exception as e:
                print('u_l_d插入错误：数据库错误', e)
                return False


# ①：输入user_id
# ②：输入device_id
# ③：输入user_id和device_id
def delete_user_link_dev(user_id=None, device_id=None):
        exist = search_user_link_dev(user_id=user_id, device_id=device_id)
        if not exist:
                print('u_l_r删除错误：删除为空！')
                return False

        sess = session()
        U_l_r = Base.classes.user_link_dev
        if user_id and device_id:
                u_l_r = sess.query(U_l_r).filter_by(user_id=user_id, device_id=device_id).all()
        elif user_id:
                u_l_r = sess.query(U_l_r).filter_by(user_id=user_id).all()
        elif device_id:
                u_l_r = sess.query(U_l_r).filter_by(device_id=device_id).all()
        else:
                print('u_l_d删除错误：输入为空')
                return False

        try:
                for i in u_l_r:
                        sess.delete(i)
                sess.commit()
                sess.close()
                print('u_l_d删除成功')
                return True
        except Exception as e:
                sess.rollback()
                print('u_l_d删除错误：数据库错误', e)
                return False


def search_user_link_dev(user_id=None, device_id=None):
        sess = session()
        U_l_r = Base.classes.user_link_dev

        if user_id and device_id:
                u_l_r = sess.query(U_l_r).filter_by(user_id=user_id, device_id=device_id).all()
        elif user_id:
                u_l_r = sess.query(U_l_r).filter_by(user_id=user_id).all()
        elif device_id:
                u_l_r = sess.query(U_l_r).filter_by(device_id=device_id).all()
        else:
                u_l_r = sess.query(U_l_r).all()

        if not u_l_r:
                print('u_l_r查找为空')
                return False
        u_l_r_dump = [{'id': u_l_r[i].id,
                       'user_id': u_l_r[i].user_id, 'device_id': u_l_r[i].device_id}
                      for i in range(len(u_l_r))]
        try:
                sess.commit()
                sess.close()
                # print(u_l_r_dump)
                return u_l_r_dump
        except Exception as e:
                print('u_l_r查找错误：数据库错误', e)
                return False


#############################################
#               猪栏设备关联表                 #
#############################################
#
def insert_swinery_link_dev(swinery_id, device_id):
        exist = search_swinery_link_dev(swinery_name=swinery_id, device_id=device_id)
        if exist:
                print('s_l_d插入错误：该条已存在！')
                return False

        sess = session()
        S_l_d = Base.classes.swinery_link_dev

        s_l_d = S_l_d(swinery_name=swinery_id, device_id=device_id)

        try:
                sess.add(s_l_d)
                sess.commit()
                sess.close()
                print('s_l_d添加成功！')
                return True
        except Exception as e:
                print('s_l_d添加错误：数据库错误', e)
                return False


# ①：swinery_id
# ②：seinery_id, device_id
def search_swinery_link_dev(swinery_name, device_id=None):
        sess = session()
        S_l_d = Base.classes.swinery_link_dev

        query = sess.query(S_l_d)

        if device_id:
                s_l_d = query.filter_by(swinery_name=swinery_name, device_id=device_id).all()
        else:
                s_l_d = query.filter_by(swinery_name=swinery_name).all()

        s_l_d_dump = [{'id': s_l_d[i].id, 'swinery_id': s_l_d[i].swinery_name, 'device_id': s_l_d[i].device_id}
                      for i in range(len(s_l_d))]
        try:
                sess.commit()
                sess.close()
                print(s_l_d_dump)
                return s_l_d_dump
        except Exception as e:
                print('s_l_d查询错误：数据库错误', e)
                return False


def delete_swinery_link_dev(swinery_name, device_id):
        exist = search_swinery_link_dev(swinery_name=swinery_name, device_id=device_id)
        if not exist:
                print('s_l_d删除错误：不存在的关系！')
                return False

        sess = session()
        S_l_d = Base.classes.swinery_link_dev

        s_l_d = sess.query(S_l_d).filter_by(swinery_name=swinery_name, device_id=device_id).first()
        try:
                sess.delete(s_l_d)
                sess.commit()
                sess.close()
                print('s_l_d删除成功！')
                return True
        except Exception as e:
                print('s_l_d删除错误：数据库错误：', e)
                sess.rollback()
                return False


#################################################################
#                       合同设备关系表                             #
#################################################################
#
# contract_id, device_id, (add_name), (is_valid)
def insert_contract_link_dev(contract_id, device_id, addname=None, is_valid=True):
        exist = search_contract_link_dev(contract_id=contract_id, device_id=device_id)
        if exist:
                print('c_l_d添加错误：数据已存在')
                return False

        sess = session()
        C_l_d = Base.classes.contract_link_dev

        # 函数内自动添加时间：××××--××--××
        now = datetime.datetime.now()
        date = datetime.datetime.date(now)
        c_l_d = C_l_d(contract_id=contract_id,
                      device_id=device_id,
                      bind_time=date,
                      addname=addname,
                      is_valid=is_valid)
        try:
                sess.add(c_l_d)
                sess.commit()
                sess.close()
                print('c_l_d添加成功！')
                return True
        except Exception as e:
                print('c_l_d添加错误：数据库错误', e)
                return False


# contract_id, device_id
def search_contract_link_dev(contract_id, device_id=None):
        sess = session()
        C_l_d = Base.classes.contract_link_dev

        if device_id:
                c_l_d = sess.query(C_l_d).filter_by(contract_id=contract_id, device_id=device_id).all()
        else:
                c_l_d = sess.query(C_l_d).filter_by(contract_id=contract_id).all()
        if not c_l_d:
                print('c_l_d查找错误：数据不存在')
                return False

        c_l_d_dump = [{'id': c_l_d[i].id, 'contract_id': c_l_d[i].contract_id,
                       'device_id': c_l_d[i].device_id, 'bind_time': c_l_d[i].bind_time,
                       'addname': c_l_d[i].addname, 'is_valid': c_l_d[i].is_valid}
                      for i in range(len(c_l_d))]
        try:
                sess.commit()
                sess.close()
                # print(c_l_d_dump)
                return c_l_d_dump
        except Exception as e:
                print('c_l_d查找错误：数据库错误', e)
                return False


def delete_contract_link_dev(contract_id, device_id):
        exist = search_contract_link_dev(contract_id=contract_id, device_id=device_id)
        if not exist:
                print('c_l_d删除错误：数据不存在')
                return False

        sess = session()
        C_l_d = Base.classes.contract_link_dev

        c_l_d = sess.query(C_l_d).filter_by(contract_id=contract_id, device_id=device_id).all()

        try:
                for i in c_l_d:
                        sess.delete(i)
                sess.commit()
                sess.close()
                print('c_l_d删除成功')
                return True
        except Exception as e:
                print('c_l_d删除错误：数据库错误', e)
                return False


# 修改is_valid和addname
# contract_id, device_id, is_valid
# contract_id, device_id, is_valid, addname
def update_contract_link_dev(contract_id, device_id, is_valid, addname=None):
        sess = session()
        C_l_d = Base.classes.contract_link_dev

        c_l_d = sess.query(C_l_d).filter_by(contract_id=contract_id, device_id=device_id).first()
        if addname:
                c_l_d.is_valid = is_valid
                c_l_d.addname = addname
        else:
                c_l_d.is_valid = is_valid

        try:
                sess.commit()
                sess.close()
                print('c_l_d修改成功')
                return True
        except Exception as e:
                print('c_l_d修改错误：数据库错误', e)
                sess.rollback()
                return False


# if __name__ == '__main__':
        # insert_dev(deviceSN='00010129214KK', cpuid='SSS1234273985798', status='B')
        # delete_dev_param(id='1')
        # update_dev('A', id=12)
        # search_dev(status='v')
        # you = device()
        # you.create_a_device(deviceSN='sasdjhoi3ru8298', cpuid='123142359')
        # insert_dev_statistics_info(15)
        # update_dev_statistics_info(15, **{'str_trig_num': 1, 'ultr_ran_num': 2,
        #                                   'equi_acc_mat': 888.9, 'equi_acc_wat': 2.2})
        # update_dev_param(device_id=14, **{'wat_22break': 12})
        # k = search_dev_statistics_info(device_id=14)

        # search_dev_param(device_id=14)
        # insert_user_link_dev(user_id=1, device_id=1)
